"""
Case Type   : ustore_DQL_hint
Case Name   : 从本地向catalog_sales表中导入数据
Create At   : 2022-04-10
Owner       : lonely-dance
Description :
    1、使用copy命令插入数据
    2、环境清理
Expect      :
    1、插入数据成功
    2、清理成功
History     :
"""

import os
import subprocess
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class HintAdd(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----- {os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = Node('PrimaryRoot')
        self.pri_node = Node('PrimaryDbUser')
        self.com = Common()
        self.constant = Constant()
        self.t_name1 = 't_col_catalog_sales'
        self.t_name2 = 't_catalog_sales'
        self.copy_file = 'catalog_sales_hint.txt'
        self.cur_path = os.path.join(macro.SCRIPTS_PATH, self.copy_file)
        self.parent_path = os.path.dirname(macro.DB_INSTANCE_PATH)
        self.target_path = os.path.join(self.parent_path, self.copy_file)

    def test_join(self):
        text = '-----step1:创建表 expect: 创建表成功-----'
        self.log.info(text)
        cmd_1 = f"""
            drop table if exists {self.t_name1};
            create table {self.t_name1}
            (
                cs_sold_date_sk           integer                       ,
                cs_sold_time_sk           integer                       ,
                cs_ship_date_sk           integer                       ,
                cs_bill_customer_sk       integer                       ,
                cs_bill_cdemo_sk          integer                       ,
                cs_bill_hdemo_sk          integer                       ,
                cs_bill_addr_sk           integer                       ,
                cs_ship_customer_sk       integer                       ,
                cs_ship_cdemo_sk          integer                       ,
                cs_ship_hdemo_sk          integer                       ,
                cs_ship_addr_sk           integer                       ,
                cs_call_center_sk         integer                       ,
                cs_catalog_page_sk        integer                       ,
                cs_ship_mode_sk           integer                       ,
                cs_warehouse_sk           integer                       ,
                cs_item_sk                integer               not null,
                cs_promo_sk               integer                       ,
                cs_order_number           bigint                not null,
                cs_quantity               integer                       ,
                cs_wholesale_cost         decimal(7,2)                  ,
                cs_list_price             decimal(7,2)                  ,
                cs_sales_price            decimal(7,2)                  ,
                cs_ext_discount_amt       decimal(7,2)                  ,
                cs_ext_sales_price        decimal(7,2)                  ,
                cs_ext_wholesale_cost     decimal(7,2)                  ,
                cs_ext_list_price         decimal(7,2)                  ,
                cs_ext_tax                decimal(7,2)                  ,
                cs_coupon_amt             decimal(7,2)                  ,
                cs_ext_ship_cost          decimal(7,2)                  ,
                cs_net_paid               decimal(7,2)                  ,
                cs_net_paid_inc_tax       decimal(7,2)                  ,
                cs_net_paid_inc_ship      decimal(7,2)                  ,
                cs_net_paid_inc_ship_tax  decimal(7,2)                  ,
                cs_net_profit             decimal(7,2)
            )
            WITH (STORAGE_TYPE=USTORE) partition by range(cs_sold_date_sk)
            (
                    partition p1 values less than(2451179),
                    partition p2 values less than(2451544),
                    partition p3 values less than(2451910),
                    partition p4 values less than(2452275),
                    partition p5 values less than(2452640),
                    partition p6 values less than(2453005),
                    partition p7 values less than(maxvalue)
            );
            """
        self.log.info(cmd_1)

        cmd_2 = f"""
            drop table if exists {self.t_name2};
            create table  {self.t_name2}
            (
                cs_sold_date_sk           integer                       ,
                cs_sold_time_sk           integer                       ,
                cs_ship_date_sk           integer                       ,
                cs_bill_customer_sk       integer                       ,
                cs_bill_cdemo_sk          integer                       ,
                cs_bill_hdemo_sk          integer                       ,
                cs_bill_addr_sk           integer                       ,
                cs_ship_customer_sk       integer                       ,
                cs_ship_cdemo_sk          integer                       ,
                cs_ship_hdemo_sk          integer                       ,
                cs_ship_addr_sk           integer                       ,
                cs_call_center_sk         integer                       ,
                cs_catalog_page_sk        integer                       ,
                cs_ship_mode_sk           integer                       ,
                cs_warehouse_sk           integer                       ,
                cs_item_sk                integer               not null,
                cs_promo_sk               integer                       ,
                cs_order_number           bigint                not null,
                cs_quantity               integer                       ,
                cs_wholesale_cost         decimal(7,2)                  ,
                cs_list_price             decimal(7,2)                  ,
                cs_sales_price            decimal(7,2)                  ,
                cs_ext_discount_amt       decimal(7,2)                  ,
                cs_ext_sales_price        decimal(7,2)                  ,
                cs_ext_wholesale_cost     decimal(7,2)                  ,
                cs_ext_list_price         decimal(7,2)                  ,
                cs_ext_tax                decimal(7,2)                  ,
                cs_coupon_amt             decimal(7,2)                  ,
                cs_ext_ship_cost          decimal(7,2)                  ,
                cs_net_paid               decimal(7,2)                  ,
                cs_net_paid_inc_tax       decimal(7,2)                  ,
                cs_net_paid_inc_ship      decimal(7,2)                  ,
                cs_net_paid_inc_ship_tax  decimal(7,2)                  ,
                cs_net_profit             decimal(7,2)
             )
            WITH (STORAGE_TYPE=USTORE) partition by range(cs_sold_date_sk)
            (
                    partition p1 values less than(2451179),
                    partition p2 values less than(2451544),
                    partition p3 values less than(2451910),
                    partition p4 values less than(2452275),
                    partition p5 values less than(2452640),
                    partition p6 values less than(2453005),
                    partition p7 values less than(maxvalue)
            );
            """
        self.log.info(cmd_2)
        res_1 = self.pri_sh.execut_db_sql(cmd_1)
        self.log.info(res_1)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res_1, "表创建失败")
        res_2 = self.pri_sh.execut_db_sql(cmd_2)
        self.log.info(res_2)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res_2, "表创建失败")

        step_txt = '-----step2:使用copy命令插入数据，expect: 数据插入成功-----'
        self.log.info(step_txt)
        text = '-----step2.1: 获取当前主机的IP地址;expect: 成功-----'
        self.log.info(text)
        cmd = "hostname -I"
        self.log.info(cmd)
        self.cur_ip = subprocess.getoutput(cmd).strip()
        self.log.info(self.cur_ip)

        text = '-----step2.2: 复制文件到数据库主机对应目录 expect: 成功-----'
        self.log.info(text)
        if self.pri_sh.node.ssh_host in self.cur_ip:
            self.log.info('当前主机是数据库主机，无需远程复制')
            cmd = f"cp {self.cur_path} {self.parent_path};"
            self.log.info(cmd)
            res = self.pri_sh.node.sh(cmd).result()
            self.log.info(res)
        else:
            self.com.scp_file(self.pri_node, self.copy_file,
                              self.parent_path)

        text = '-----step2.3: 查看数据库主机目标路径下文件 expect: 成功-----'
        self.log.info(text)
        cmd = f"ls -l {self.parent_path}| grep -E '{self.copy_file}'"
        self.log.info(cmd)
        res = self.pri_root.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('No such file or directory', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file}', res, '文件拷贝失败')

        text = '-----step2.4:使用copy命令插入数据，expect: 数据插入成功-----'
        self.log.info(text)
        self.log.info('---------------使用copy命令插入数据------------------')
        copy_sql = f"copy {self.t_name1} from '{self.target_path}'; "
                   
        self.log.info(copy_sql)
        copy_result = self.pri_sh.execut_db_sql(copy_sql)
        self.log.info(copy_result)
        self.assertEqual(copy_result.strip().splitlines()[0].strip(),
                         'COPY 50', '执行失败:' + step_txt)

        text = '-----step2.5:使用copy命令插入数据，expect: 数据插入成功-----'
        self.log.info(text)
        self.log.info('---------------使用copy命令插入数据------------------')
        copy_sql = f"copy {self.t_name2} from '{self.target_path}'; "
                   
        self.log.info(copy_sql)
        copy_result = self.pri_sh.execut_db_sql(copy_sql)
        self.log.info(copy_result)
        self.assertEqual(copy_result.strip().splitlines()[0].strip(),
                         'COPY 50', '执行失败:' + step_txt)

    def tearDown(self):
        self.log.info('-----this is teardown-----')
        step_txt = '-----step3: 环境清理，expect:清理成功-----'
        self.log.info(step_txt)
        self.log.info('由于后续用例依赖表，故先不删除')
        rm_cmd = f"rm -rf {self.target_path} "
        self.log.info(rm_cmd)
        rm_res = self.pri_root.sh(rm_cmd).result()
        self.log.info(rm_res)
        self.assertEqual(rm_res, '', '文件删除失败' + step_txt)
        self.log.info(f'----- {os.path.basename(__file__)} end-----')
